﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace ScrumProject.Models
{
    public class TaskRepository: IDisposable
    {
        SMRPO6Context db = new SMRPO6Context();
        
        public void AddTask(Task task)
        {
            using (var db = new SMRPO6Context())
            {
                db.Database.ExecuteSqlCommand("exec dbo.AddTask @StoryId, @Description, @WorkRemaining, @Status, @UserId",
                                              new SqlParameter("StoryId", task.StoryId),
                                              new SqlParameter("Description", task.Description),
                                              new SqlParameter("WorkRemaining", task.WorkRemaining),
                                              new SqlParameter("Status", task.Status),
                                              new SqlParameter("UserId", task.UserId));

                db.SaveChanges();

            }
            
        }

        public void UpdateTask(Task task)
        {
            using (var db = new SMRPO6Context())
            {
                db.Database.ExecuteSqlCommand("exec dbo.UpdateTask @Id, @StoryId, @Description, @WorkRemaining, @Status, @UserId",
                                              new SqlParameter("Id", task.Id),
                                              new SqlParameter("StoryId", task.StoryId),
                                              new SqlParameter("Description", task.Description),
                                              new SqlParameter("WorkRemaining", task.WorkRemaining),
                                              new SqlParameter("Status", task.Status),
                                              new SqlParameter("UserId", task.UserId));

                db.SaveChanges();

            }
            
        }

        public void UpdateTaskCurrentStatus(Task task)
        {   
            using (var db = new SMRPO6Context())
            {

                db.Database.ExecuteSqlCommand("exec dbo.UpdateTaskStatus @Id,@Status,@UserId",
                                                new SqlParameter("Id", task.Id),
                                                new SqlParameter("Status", task.Status),
                                                new SqlParameter("UserId", task.UserId));

                db.SaveChanges();

            }
        }

        public void UpdateTaskStatus(Task task)
        {
            if (task.WorkRemaining == 0)
            {

                using (var db = new SMRPO6Context())
                {

                    db.Database.ExecuteSqlCommand("exec dbo.UpdateTaskStatus @Id,@Status",
                                                  new SqlParameter("Id", task.Id),
                                                  new SqlParameter("Status", task.Status));

                    db.SaveChanges();

                }
            }
        }
        public void UpdateTaskRemainingWork(Task task)
        {
            
                using (var db = new SMRPO6Context())
                {

                    db.Database.ExecuteSqlCommand("exec dbo.UpdateTaskRemainingWork @Id,@WorkRemaining",
                                                  new SqlParameter("Id", task.Id),
                                                  new SqlParameter("WorkRemaining", task.WorkRemaining));

                    db.SaveChanges();

                }
            
        }
        public void UpdateTaskRemainingWork(int id,double work)
        {

            using (var db = new SMRPO6Context())
            {

                db.Database.ExecuteSqlCommand("exec dbo.UpdateTaskRemainingWork @Id,@WorkRemaining",
                                              new SqlParameter("Id", id),
                                              new SqlParameter("WorkRemaining", work));

                db.SaveChanges();

            }

        }
        public IQueryable<Task> GetAllTasks()
        {
            return db.Tasks;
        }

        public IQueryable<Task> GetTasksByUserAndProject(int id, int status, int projectId)
        {
            return from Task in db.Tasks
                   join User in db.Users on Task.UserId equals User.Id
                   join Story in db.Stories on Task.StoryId equals Story.Id
                   where Task.UserId == id && Task.Status == status && Story.ProjectId == projectId
                   select Task;
        }

        public IQueryable<Task> GetTasksByUserAndProject(int id, int projectId)
        {
            return from Task in db.Tasks
                   join User in db.Users on Task.UserId equals User.Id
                   join Story in db.Stories on Task.StoryId equals Story.Id
                   where Task.UserId == id && (Task.Status == 1 || Task.Status == 3) && Story.ProjectId == projectId
                   select Task;
        }

        public int? GetProjectId(Task task) 
        {
            return (from Task in db.Tasks
                   join Story in db.Stories on Task.StoryId equals Story.Id
                   select Story.ProjectId).FirstOrDefault();
        }

        public DateTime GetSprintEndDate(Task task) 
        {
            return (from Task in db.Tasks
                    join Story in db.Stories on Task.StoryId equals Story.Id
                    join Sprint in db.Sprints on Story.SprintId equals Sprint.Id
                    select Sprint.DateTo).FirstOrDefault();
        }

        public IQueryable<Task> GetTasksByUser(int id, int status)
        {
            return from Task in db.Tasks
                   join User in db.Users on Task.UserId equals User.Id
                   where Task.UserId == id && Task.Status == status
                   select Task;
        }

        public IQueryable<Task> GetAllTasksByStatus(int id, int status)
        {

            return from Task in db.Tasks
                   where Task.StoryId == id && Task.Status == status
                   select Task;
        }
        public Task GetTask(int id)
        {
            return (from Task in db.Tasks
                    where Task.Id == id
                    select Task).SingleOrDefault();
        }

        public string GetStoryName(int id)
        {
            return (from Story in db.Stories
                    where Story.Id == id
                    select Story.Name).SingleOrDefault();
        }

        
        public IQueryable<Task> GetTasksByStory(int sid)
        {
            return from Task in db.Tasks
                    where  Task.StoryId == sid
                    select Task;
        }

        public void DeleteStory(Task task)
        {

            using (var db = new SMRPO6Context())
            {
                db.Tasks.Remove(task);
                db.SaveChanges();
            }

        }

        public void Save()
        {
            db.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    db.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}